package com.bshinfo.web.base.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil
{
	private static DecimalFormat df = new DecimalFormat("###0.000000");
	public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
	public static Workbook web;
	public static XSSFWorkbook xssfweb;
	public static HSSFWorkbook hssfweb;
	public static FormulaEvaluator evaluator;
	private static int version = 2003;
	
	public static  Object objtyp ;
	//判断excel是2007或者2003
	public static void getVersion(String path,String Operatrion) throws IOException 
	{
		if(path.endsWith(".xls"))
		{
			version = 2003;
		}
		else if(path.endsWith(".xlsx"))
		{
			version =2007;
		}
		if(Operatrion.equals("read"))
		{
			InputStream stream = new FileInputStream(path);
			if(version == 2003)
			{
				web = new  HSSFWorkbook(stream);
				evaluator = web.getCreationHelper().createFormulaEvaluator();
			}else if(version == 2007)
			{
				web = new XSSFWorkbook(path);
				evaluator = web.getCreationHelper().createFormulaEvaluator();
			}
		}
		else if(Operatrion.equals("write"))
		{
			if(version == 2003)
			{
				web =(Workbook) new  HSSFWorkbook(new FileInputStream(path));
			}else if(version == 2007)
			{
				web = (Workbook) new XSSFWorkbook(new FileInputStream(path));
			}
		}
	 }

	@SuppressWarnings("resource")
	public static Workbook getVersionWorkbook(String path,String Operatrion) throws IOException 
	{
		Workbook web=null;
		if(path.endsWith(".xls"))
		{
			version = 2003;
		}
		else if(path.endsWith(".xlsx"))
		{
			version =2007;
		}
		if(Operatrion.equals("read"))
		{
			InputStream stream = new FileInputStream(path);
			if(version == 2003)
			{
				web = new  HSSFWorkbook(stream);
				evaluator = web.getCreationHelper().createFormulaEvaluator();
			}else if(version == 2007)
			{
				web = new XSSFWorkbook(path);
				evaluator = web.getCreationHelper().createFormulaEvaluator();
			}
		}
		else if(Operatrion.equals("write"))
		{
			if(version == 2003)
			{
				web =(Workbook) new  HSSFWorkbook(new FileInputStream(path));
			}else if(version == 2007)
			{
				web = (Workbook) new XSSFWorkbook(new FileInputStream(path));
			}
		}
		return web;
	 }
	
	//取Cell数据，参数 int double 
	public static String getCellValue(Cell xCell,String dataType)
	{   
		if(xCell == null) return "";
		if (xCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
		{			
			if(dataType=="double")
			{
				return df.format(xCell.getNumericCellValue());
			}
			else if(dataType=="int")
			{
				return String.valueOf((int) xCell.getNumericCellValue());
			}
			else if(dataType=="date")
			{
				return sdf2.format(xCell.getDateCellValue());
			}
			else
			{
				return String.valueOf( xCell.getNumericCellValue());
			}
				
		}
		else if (xCell.getCellType() == Cell.CELL_TYPE_FORMULA)
		{
			switch (xCell.getCellType())
			{
				 case 0: 
					 return String.valueOf(xCell.getNumericCellValue()); 
				    
			     case 1: 
			    	 return xCell.getStringCellValue(); 
				    
			     case 2: 
			    	 return String.valueOf(xCell.getNumericCellValue());
				  
			     default: 
			    	 return xCell.getStringCellValue(); 

			}

		}
		else
		{
			return String.valueOf(xCell.getStringCellValue());
		}
	}
	
	/**
	 * 将String类型转换为double,自动计算并保存一位小数
	 * @param string
	 * @return
	 */
	public static String autoCount(String string,double f){
        
		BigDecimal   b  =   new BigDecimal(Double.parseDouble(string)/f);  
		//四舍五入保留一位小数
		return String.valueOf(b.setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue());
	}
	
}

